
/*jslint */
/*global openDatabase: true, $: true */
"use strict";

var dataService = (function () {
    //Load the database
    var db;
    var oQueryOpts = new Object();


    function errorHandler(transaction, error) {
        console.log('Oops. Error was ' + error.message + ' (Code ' + error.code + ')');
        return true;
    }

    function errorHandlerSimple(error) {
        console.log('Oops. Error was ' + error.message + ' (Code ' + error.code + ')');
        return true;
    }
    return {

        ////////Query Options
        //////QueryOpts: function()
        //////{
        //////    oQueryOpts.currentLimit = 50;
        //////    oQueryOpts.currentPage = 1;
        //////    oQueryOpts.currentFilter = '';
        //////    oQueryOpts.currentSort = '';
        //////    oQueryOpts.currentSortDirection = 'ASC';
        //////},
          
        ////////Query Options
        //////QueryOpts_SetParams: function (currentLimit, currentPage, currentFilter, currentSort, currentSortDirection)
        //////{
        //////    oQueryOpts.currentLimit = 50;
        //////    oQueryOpts.currentPage = 1;
        //////    oQueryOpts.currentFilter = '';
        //////    oQueryOpts.currentSort = '';
        //////    oQueryOpts.currentSortDirection = 'ASC';
        //////},

        //Initialise the database
        startDatabase: function () {
                
            var shortName = 'dbInTouch',
                version = '1.0',
                displayName = 'dbInTouch',
                maxSize = 5242880;//65536;
                    
            db = openDatabase(shortName, version, displayName, maxSize);
            //alert('open database'); 
        },
        
        //Create a Table : Invoke the proper function for that
        createTable: function (tableName) {
            console.log('creating: ' + tableName);
            switch (tableName) {
                case appGlobalVars.table_UserCred():                //User Cred
                    this.create_Table_UCred();
                    break;
                case appGlobalVars.table_Contacts():                //Contacts
                    this.create_Table_UContacts();
                    break;  
                case appGlobalVars.table_ContactInfos():            //Contacts Info
                    this.create_Table_UContactInfos();
                    break;
                case appGlobalVars.table_Reminders():               //Reminders
                    this.create_Table_UReminders();
                    break;
                case appGlobalVars.table_ContactCategories():       //Contacts Category
                    this.create_Table_ContactCategories();
                    break;
                 default:
                    console.log('no method for table ' + tableName);
                    break;
            }
        },
        
        


        //create the UContacts table
        create_Table_UContacts: function ()
        {
            //uniqueId_<firstinitial>_<lastinitial>_datecreated

            db.transaction(
                function (transaction) {
                    transaction.executeSql(
                        'CREATE TABLE IF NOT EXISTS ' + appGlobalVars.table_Contacts() +
                        ' (Ukey INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ' +
                        ' id TEXT NOT NULL, ' +
                        ' displayName TEXT NULL, ' +
                        ' picturePath TEXT NULL, ' +
                        ' DateCreated TEXT NULL,' +
                        ' DateLastContacted TEXT NULL,' +           //date_last_contacted
                        ' MethodLastContacted TEXT NULL,' +         //method_last_contacted
                        ' DateLastSynchedPhone TEXT NULL,' +
                        ' DateLastSynchedServer TEXT NULL);',
                        [], null, errorHandler);                       
                }
            );
        },

        save_SingleContact: function (contact, successCallBack, errorCallBack)
        {
            var dateCreated= new Date();
            db.transaction(
                function (transaction) {
                    transaction.executeSql(
                          'INSERT OR REPLACE INTO ' + appGlobalVars.table_Contacts() +
                          '(id,displayName,DateCreated,DateLastSynchedPhone)' +
                          'VALUES (?,?,?,?);',
                           [contact.id, contact.displayName, dateCreated, dateCreated], 
                           function (transaction, results) {
                               console.log(results.insertId);
                               //return the ID
                               successCallBack(results.insertId);
                           }
                           , errorCallBack);
                }
            );
        },


        /**************************************************************************************/
        /*  SECTION - TABLE: CONTACTINFO   */
        /**************************************************************************************/
        //create the UContacts table
        create_Table_UContactInfos: function ()
        {
            
            db.transaction(
                function (transaction) {
                    transaction.executeSql(
                        'CREATE TABLE IF NOT EXISTS ' + appGlobalVars.table_ContactInfos() +
                        ' (Ukey INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ' +
                        ' UKey_Contact INTEGER NOT NULL, ' +
                        ' ContactInfoGroup TEXT NULL, ' +           //email,phone,facebook
                        ' ContactInfoType TEXT NULL, ' +            //work email, personal email
                        ' ContactInfo TEXT NULL, ' +
                        ' IsPref TEXT NULL, ' +
                        ' DateCreated TEXT NULL,' +
                        ' DateLastSynchedPhone TEXT NULL,' + 
                        ' DateLastSynchedServer TEXT NULL);',
                        [], null, errorHandler);                       
                }
            );
        },


        //Save Single Contact Info
        save_SingleContactInfo: function (contactInfo)
        {
            var dateCreated= new Date();
            db.transaction(
                function (transaction) {
                    transaction.executeSql(
                          'INSERT OR REPLACE INTO ' + appGlobalVars.table_ContactInfos() +
                          '(UKey_Contact,ContactInfoGroup,ContactInfoType,ContactInfo,isPref,DateCreated,DateLastSynchedPhone)' +
                          'VALUES (?,?,?,?,?,?,?);',
                           [contactInfo.UKey_Contact, contactInfo.group, contactInfo.type, contactInfo.value, contactInfo.pref, dateCreated, dateCreated], null, errorHandler);
                }
            );
        },


        /**************************************************************************************/
        /*  SECTION - TABLE: CONTACT CATEGORIES   */
        /**************************************************************************************/

        //Create Contact Categories table
        create_Table_ContactCategories: function () {
            db.transaction(
                function (transaction) {
                    transaction.executeSql(
                        'CREATE TABLE IF NOT EXISTS ' + appGlobalVars.table_ContactCategories() +
                        ' (Ukey INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ' +
                        ' UKey_Contact INTEGER NOT NULL, ' +
                        ' ContactCategory TEXT NOT NULL, ' +
                        ' DateCreated TEXT NULL,' +
                        ' DateLastUpdated TEXT NULL,' +
                        ' DateLastSynched TEXT NULL);',
                        [], null, errorHandler);
                }
            );
        },

        //Save Single Contact Info
        save_SingleContactCategories: function (oContactCategory, successCallback, errorCallback)
        {
            console.log('entering: save_SingleContactCategories');

            var dateCreated= new Date();
            db.transaction(
                function (transaction) {
                    transaction.executeSql(
                          'INSERT OR REPLACE INTO ' + appGlobalVars.table_ContactCategories() +
                          '(UKey_Contact, ContactCategory, DateUpdated)' +
                          'VALUES (?,?,?);',
                           [
                                oContactCategory.UKey_Contact, 
                                oContactCategory.contactCategory,
                                dateCreated
                           ], successCallback, errorCallback);
                }
            );
        },

   


        /**************************************************************************************/
        /*  SECTION - TABLE: USER CRED  */
        /**************************************************************************************/
        //create the UCred table
        create_Table_UCred: function () {
            db.transaction(
                function (transaction) {
                    transaction.executeSql(
                        'CREATE TABLE IF NOT EXISTS ' + appGlobalVars.table_UserCred() +
                        ' (Ukey INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ' +
                        ' DeviceId TEXT NOT NULL, ' +
                        ' UName TEXT NULL, ' +
                        ' UEmail TEXT NULL, ' +
                        ' DateCreated TEXT NULL,' +
                        ' DateLastUpdated TEXT NULL,' +
                        ' DateLastSynched TEXT NULL,' +
                        ' AutoSyncFrequency TEXT NULL,' +
                        ' UncategorizedContactTo TEXT NULL);',
                        [], null, errorHandler);
                }
            );
        },

        //initialize_Table_UCred_withKey
        initialize_Table_UCred_withKey: function (deviceKey)
        {
            //alert(deviceKey)
            var dateCreated = new Date();
            db.transaction(
                function (transaction) {
                    transaction.executeSql(
                          'INSERT OR REPLACE INTO ' + appGlobalVars.table_UserCred() +
                          '(DeviceId, DateCreated) VALUES (?,?);',
                           [deviceKey,dateCreated], null, errorHandler);                       
                }
            );
        },

        /****************************************************************************/
        


        /**************************************************************************************/
        /*  SECTION - TABLE: SETTINGS   */
        /**************************************************************************************/
       //Retrieve list of Settings
        get_Settings_FromLocalStorage: function (successCallback, errorCallBack) {

            console.log('entering: get_Settings_FromLocalStorage');

                db.transaction(
                   function (transaction) {
                       transaction.executeSql(
                            "SELECT * FROM " + appGlobalVars.table_UserCred(),
                           [],
                           function (transaction, result) {
                               var settings = [], i, row;
                               for (i = 0; i < result.rows.length; i += 1) {
                                   row = result.rows.item(i);
                                   settings.push(row);
                               }

                               if (settings.length > 0)
                                  successCallback(settings[0]);
                           }, errorCallBack);
                   }
               );
            },

        //update the user table with the item in the settings screens
        /****************************************************************************/
        //update_Table_UCred_WithSettings: function (username, useremail,
        //                                            autosyncfrequency, uncategorizedContactTo, successCallback, errorCallback)
        update_Table_UCred_WithSettings: function (oSettings, successCallback, errorCallback) {
            console.log('entering: update_Table_UCred_WithSettings');
            var dateUpdated = new Date();

            db.transaction(
                function (transaction) {
                    transaction.executeSql(
                          'UPDATE ' + appGlobalVars.table_UserCred() +
                          ' SET ' +
                          ' UName=?, ' +
                          ' UEmail=?, ' +
                          ' AutoSyncFrequency=?, ' +
                          ' UncategorizedContactTo=?, ' +
                          ' DateLastUpdated=? ' +
                          ' WHERE 1=1;',   //single table so far....
                           [
                                oSettings.username,
                                oSettings.useremail,
                                oSettings.autosyncFrequency,
                                oSettings.uncategorizedContactTo,
                                dateUpdated
                           ], successCallback, errorCallback);
                }
            );
        },



        /**************************************************************************************/
        /*  SECTION - TABLE: REMINDERS   */
        /**************************************************************************************/
        //create the UReminders table
        create_Table_UReminders: function ()
        {
            
            db.transaction(
                function (transaction) {
                    transaction.executeSql(
                        'CREATE TABLE IF NOT EXISTS ' + appGlobalVars.table_Reminders() +
                        ' (Ukey INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ' +
                        ' UKey_Contact INTEGER NOT NULL, ' +
                        ' ReminderType TEXT NULL, ' +                       //birthdate,anniversary
                        ' ReminderNoticeMethod TEXT NULL, ' +               //remind through email, remind through phone
                        ' ReminderDate TEXT NULL, ' +                       //Reminder Date
                        ' IsUserEntered TEXT NULL,' +                       //Is User Entered (from a calendar)
                        ' DateCreated TEXT NULL,' +
                        ' DateLastSynchedPhone TEXT NULL,' + 
                        ' DateLastSynchedServer TEXT NULL);',
                        [], null, errorHandler);                       
                }
            );
        },


        //save_SingleReminder: save a single Reminder
        save_SingleReminder: function (reminder, successCallBack, errorCallBack)
        {
            var dateCreated = new Date();

            db.transaction(
                function (transaction) {
                    transaction.executeSql(
                          'INSERT OR REPLACE INTO ' + appGlobalVars.table_Reminders()
                          + '('
                              + 'UKey_Contact'
                              + ',ReminderType'
                              + ',ReminderNoticeMethod'
                              + ',ReminderDate'
                              + ',IsUserEntered'
                              + ',DateCreated'
                              + ',DateLastSynchedPhone'
                              //+ ',DateLastSynchedServer'
                           + ')'
                           + 'VALUES (?,?,?,?,?,?,?);',
                           [
                             reminder.UKey_Contact,
                             reminder.ReminderType,
                             reminder.ReminderNoticeMethod,
                             reminder.ReminderDate,
                             reminder.IsUserEntered,
                             dateCreated,
                             dateCreated
                           ],
                           function (transaction, results) {
                               console.log(results.insertId);
                               //return the ID
                               successCallBack(results.insertId);
                           }
                           , errorCallBack);
                }
            );
        },


        //Retrieve list of Reminders
        get_ListOfReminders_FromLocalStorage : function (successCallback, errorCallBack)
        {
            console.log('entering: get_ListOfReminders');

            db.transaction(
               function (transaction) {
                   transaction.executeSql(
                        "SELECT * FROM " + appGlobalVars.table_Reminders(),
                       [],
                       function (transaction, result) {
                           var reminders = [], i, row;
                           for (i = 0; i < result.rows.length; i += 1) {
                               row = result.rows.item(i);
                               reminders.push(row);
                           }
                           successCallback(reminders);
                       }, errorCallBack);
               }
           );
        },




        /****************************************************************************/
        //load the contacts from local storage
        /****************************************************************************/
        getContacts_Count_FromLocalStorage: function (successCallback) {
            var totalRecords = 0;
            var _sql = "SELECT COUNT(*) as totalRecords FROM " + appGlobalVars.table_Contacts();
            db.transaction(
               function (transaction) {
                   transaction.executeSql(
                       _sql, [],
                       function (transaction, result) {
                           //var contacts = [], i, row;
                           //for (i = 0; i < result.rows.length; i += 1) {
                           //    row = result.rows.item(i);
                           //    contacts.push(row);
                           //alert(result);
                           //}
                           //alert(result.rows.item(0).totalRecords);
                           totalRecords = result.rows.item(0).totalRecords;
                           successCallback(totalRecords);
                         //  alert(totalRecords);
                       }, errorHandler);
               }
           );

            return totalRecords;
        },


        /****************************************************************************/
        //load the contacts from local storage
        /****************************************************************************/
        getContacts_FromLocalStorage: function (oQueryOpts,successCallback, errorCallBack) {
            console.log('entering: getContacts_FromLocalStorage');
            
            var _sql = "SELECT * FROM " + appGlobalVars.table_Contacts();

            //TODO:create multiple sorting fields here....
            //filtering - only by displayName for now
            if (oQueryOpts.currentFilter != '') {
                _sql += " WHERE displayName LIKE '%" + oQueryOpts.currentFilter + "%'";
            }

            //sorting
            if (oQueryOpts.currentSort != '') {
                _sql += " ORDER BY " + oQueryOpts.currentSort + " " + oQueryOpts.currentSortDirection;
            }

            //limiting
            if (oQueryOpts.currentLimit != 0) {
                _sql += " LIMIT " + oQueryOpts.currentLimit;

            }

            //paging
            if (oQueryOpts.currentOffset != 0 && oQueryOpts.currentPage != 1) {
                _sql += " OFFSET " + (oQueryOpts.currentOffset * (oQueryOpts.currentPage-1));
             }

            //alert(oQueryOpts.totalRecords);

            //before we started this querying thing
            //'SELECT * FROM ' + appGlobalVars.table_Contacts() + ' ORDER BY displayName LIMIT 50 OFFSET 50'
            //console.log('retrieving using this sql: ' + _sql);
            //alert('retrieving using this sql: ' + _sql);
            db.transaction(
                function (transaction) {
                    transaction.executeSql(
                        _sql, [],
                        function (transaction, result) {
                            var contacts = [], i, row;
                            for (i = 0; i < result.rows.length; i += 1) {
                                row = result.rows.item(i);
                                contacts.push(row);
                            }
                            successCallback(contacts);
                        }, errorCallBack);
                }
            );
        },

        /****************************************************************************/
        //load single contact from local storage
        /****************************************************************************/
        get_SingleContact_FromLocalStorage: function (Ukey,successCallback, errorCallBack) {
            console.log('entering: get_SingleContact_FromLocalStorage');
            var _sql = 'SELECT * FROM ' + appGlobalVars.table_Contacts() + ' WHERE Ukey=?'
            console.log('my sql: '+ _sql)

            db.transaction(
                function (transaction) {
                    transaction.executeSql(
                        _sql,
                        [Ukey],
                        function (transaction, result) {
                            var oSingleContact;
                            oSingleContact =result.rows.item(0);
                            successCallback(oSingleContact);
                        }, errorHandler);
                }
            );
        },
        /****************************************************************************/
        //load contact info details for single contact from local storage
        /****************************************************************************/
        get_SingleContact_Infos_FromLocalStorage: function (Ukey,successCallback, errorCallBack) {
            console.log('entering: get_SingleContact_Infos_FromLocalStorage');
            var _sql = 'SELECT * FROM ' + appGlobalVars.table_ContactInfos() + ' WHERE UKey_Contact=?'
            console.log('my sql: '+ _sql)

            db.transaction(
                function (transaction) {
                    transaction.executeSql(
                        _sql,
                        [Ukey],
                        function (transaction, result) {
                            var contactInfos = [], i, row;
                            for (i = 0; i < result.rows.length; i += 1) {
                                row = result.rows.item(i);
                                contactInfos.push(row);
                            }
                            successCallback(contactInfos);
                        }, errorHandler);
                }
            );
        },
        /****************************************************************************/
        //update the picture for the contact in the local storage
        /****************************************************************************/
        update_Table_UContacts_withPicture: function (UKey, newPicturePath)
        {
            //alert(deviceKey)
            db.transaction(
                function (transaction) {
                    transaction.executeSql(
                          'UPDATE ' + appGlobalVars.table_Contacts() +
                          ' SET picturePath=?' +
                          ' WHERE Ukey=?;',
                           [newPicturePath,UKey], null, errorHandler);                       
                }
            );
        },

        /****************************************************************************/
        //update last contacted
        /****************************************************************************/
        update_Table_UContacts_Reset_LastContacted: function (UKey, lastMethod, successCallback, errorCallBack)
        {
            var dateLastContacted = new Date();

            db.transaction(
                function (transaction) {
                    transaction.executeSql(
                          'UPDATE ' + appGlobalVars.table_Contacts() +
                          ' SET ' + 
                          ' DateLastContacted=?, ' +
                          ' MethodLastContacted=?' +
                          ' WHERE Ukey=?;',
                           [dateLastContacted, lastMethod, UKey], successCallback (dateLastContacted), errorHandler);
                }
            );
        },


        //Create a category entry in the database
        createContact: function (entry) {
            
            var noPicture = "";
  
          
            if(online){
                db.transaction(
                    function (transaction) {
                        transaction.executeSql(
                             'INSERT OR REPLACE INTO contacts (fullName, url, memberId, location, status, picture, company, position, past, current) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);',
                          [entry.fullName, entry.url, entry.memberId, entry.location, entry.status, noPicture, entry.company, entry.position, entry.past , entry.current], null, errorHandler
                    );
                }
            );
            return false;
            }
        },
        
             
        //get all child categories of 'path' from database
        getContacts: function (callback) {
            
            db.transaction(
                function (transaction) {
                    transaction.executeSql(
                        'SELECT * FROM contacts', [],
                        function (transaction, result) {                        
                            var contacts = [], i, row;
                            for (i = 0; i < result.rows.length; i += 1) {
                                row = result.rows.item(i);
                                contacts.push(row);
                            }                     
                            callback(contacts);
                        }, errorHandler);
                }
            );
        },
        
        
        // Delete table from database
        dropTable: function (tableName) {
            console.log('dropping ' + tableName);
         db.transaction(
             function(transaction) {
                 transaction.executeSql("DROP TABLE " + tableName, [], 
                     function () {
                        //alert('done dropping ' + tableName);
                     }
                     , function () {
                        //alert('failed in dropping ' + tableName);
                     });
             }
         );
      },


        //isTableExists
      isTableExists: function (tableName, callback) {
          try {
          //alert('isTableExists - searching for: ' + tableName);
          db.transaction(
              function(transaction){
                  transaction.executeSql('SELECT * FROM ' + tableName, [], function (tx, resultSet) {
                      if (resultSet.rows.length <= 0) {
                          callback(false,'no data');
                      } else {
                          callback(true, '');
                      }
                  }, function (transaction,err) {
                       errorHandler(transaction,err)
                       callback(false, err.message);
                  }); //transaction.executeSql
              });
          } catch (e) {
              errorHandlerSimple(e.description);
          }
      }//END isTableExists

      //  //isTableExists
      //isTableExists: function (tableName) {
      //    //TODO:Fix here please, no hardcoding
      //    //return false;
      //    try {
      //        console.log('isTableExists - searching for: ' + tableName);
      //        db.transaction(
      //            function (transaction) {
      //                transaction.executeSql('SELECT * FROM ' + tableName, [], function (tx, resultSet) {
      //                    if (resultSet.rows.length <= 0) {
      //                        console.log('does not exist');
      //                        return false;
      //                    } else {
      //                        console.log('exists');
      //                        return true;
      //                    }
      //                }, function (transaction, err) {
      //                    errorHandler(transaction, err)
      //                    console.log('does not exist ' + err);
      //                    return false;
      //                }); //transaction.executeSql
      //            });
      //    } catch (e) {
      //        errorHandlerSimple(e.description);
      //    }
      //}//END isTableExists
              
      
    };
}());

